package ctn.dao.hibernate.impl;

import java.sql.SQLException;
import java.util.Collections;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.stereotype.Component;

import ctn.dao.inteface.SpecDao;
import ctn.hibernate.po.CtnCpaSpec;
import ctn.util.CPAUtil;
import ctn.util.jmesa.DTOFilter;
import ctn.util.jmesa.DTOSorter;
import ctn.util.jmesa.PaginationCriteria;
@Component
public class SpecDaoImpl extends AbstractCTNDAO  implements SpecDao {
	@Autowired
	private CPAUtil cpaUtil;
	@Override
	public List<CtnCpaSpec> getListSpec(final PaginationCriteria criteria) {
		// TODO Auto-generated method stub
		return getHibernateTemplate().execute(new HibernateCallback<List<CtnCpaSpec>>(){

			@Override
			public List<CtnCpaSpec> doInHibernate(Session session)
					throws HibernateException, SQLException {
				StringBuffer hql=new StringBuffer("FROM CtnCpaSpec a where 1=1 ");
				setFilterForSpec(criteria,hql);
				setSorterForSpec(criteria,hql);
				Query query=session.createQuery(hql.toString().substring(0,hql.toString().length()-1));
				setParameterForSpec(criteria,query);
				query.setMaxResults(criteria.getRowEnd()-criteria.getRowStart());
				query.setFirstResult(criteria.getRowStart());
				List<CtnCpaSpec> list=query.list();
				return list;
			}
			
		});
	}

	@Override
	public Long getListSpecCount(final PaginationCriteria criteria) {
		
		return getHibernateTemplate().execute(new HibernateCallback<Long>(){

			@Override
			public Long doInHibernate(Session session) throws HibernateException,
					SQLException {
				StringBuffer hql=new StringBuffer("Select Count(*) FROM CtnCpaSpec a where 1=1 ");
				setFilterForSpec(criteria,hql);
				setSorterForSpec(criteria,hql);
				Query query=session.createQuery(hql.toString().substring(0,hql.toString().length()-1));
				setParameterForSpec(criteria,query);
				return (Long) query.uniqueResult();
			}

			

		
			
		});
	}
	private void setParameterForSpec(PaginationCriteria criteria,
			Query query) {
		List<DTOFilter> filters=criteria.getPageFilter().getFilters();
		for(DTOFilter filter:filters){
			if("funName".equals(filter.getProperty())){
				query.setString("funName", "%"+filter.getValue()+"%");
			}else if("proName".equals(filter.getProperty())){
				query.setString("proName", "%"+filter.getValue()+"%");
			}else if("specTyp".equals(filter.getProperty())){
				query.setString("specTyp", "%"+filter.getValue()+"%");
			}else if("specName".equals(filter.getProperty())){
				query.setString("specName", "%"+filter.getValue()+"%");
			}else if("createUserName".equals(filter.getProperty())){
				query.setString("name", "%"+filter.getValue()+"%");
			}else if("createDate".equals(filter.getProperty())){
				query.setParameter("createDate", filter.getValue());
			}else if("funid".equals(filter.getProperty())){
				query.setParameter("funid", filter.getValue());
			}else if("verid".equals(filter.getProperty())){
				query.setParameter("verid", filter.getValue());
			}
		}
	}
	private void setSorterForSpec(PaginationCriteria criteria,
			StringBuffer hql) {
		List<DTOSorter> sorters=criteria.getPageSorter().getSorts();
		hql.append(" order by ");
		if(sorters.isEmpty()){
			hql.append("a.createDate DESC,");
		}
		for(DTOSorter sort:sorters){
			if("funName".equals(sort.getProperty())){
				hql.append(" a.cpaFun.funName ").append(sort.getOrder()).append(",");
			}else if("proName".equals(sort.getProperty())){
				hql.append(" a.ctnCpaVer.ctnCpaInfo.name ").append(sort.getOrder()).append(",");
			}else if("specTyp".equals(sort.getProperty())){
				hql.append(" a.specTyp ").append(sort.getOrder()).append(",");
			}else if("specName".equals(sort.getProperty())){
				hql.append(" a.fileName ").append(sort.getOrder()).append(",");
			}else if("createUserName".equals(sort.getProperty())){
				hql.append(" a.ctnUser.name ").append(sort.getOrder()).append(",");
			}else if("createDate".equals(sort.getProperty())){
				hql.append(" a.createDate ").append(sort.getOrder()).append(",");
			}
		
			else if("funid".equals(sort.getProperty())){
				hql.append(" a.cpaFun.id ").append(sort.getOrder()).append(",");
			}else if("verid".equals(sort.getProperty())){
				hql.append(" a.ctnCpaVer.id ").append(sort.getOrder()).append(",");
			}
		}
	}

	private void setFilterForSpec(PaginationCriteria criteria,
			StringBuffer hql) {
		List<DTOFilter> filters=criteria.getPageFilter().getFilters();
		for(DTOFilter filter:filters){
			if("funName".equals(filter.getProperty())){
				hql.append(" AND a.cpaFun.funName like :funName ");
			}else if("proName".equals(filter.getProperty())){
				hql.append(" AND a.ctnCpaVer.ctnCpaInfo.name like :proName ");
			}else if("specTyp".equals(filter.getProperty())){
				hql.append(" AND a.specTyp like :specTyp ");
			}else if("specName".equals(filter.getProperty())){
				hql.append(" AND a.fileName like :specName ");
			}else if("createUserName".equals(filter.getProperty())){
				hql.append(" AND a.ctnUser.name like :name ");
			}else if("createDate".equals(filter.getProperty())){
				hql.append(" AND a.createDate =:createDate ");
			}
			else if("funid".equals(filter.getProperty())){
				hql.append(" AND a.cpaFun.id =:funid ");
			}else if("verid".equals(filter.getProperty())){
				hql.append(" AND a.ctnCpaVer.id =:verid ");
			}
		}
	}
	@Override
	public String getSpecFilePath(String id) {
		CtnCpaSpec spec=getHibernateTemplate().load(CtnCpaSpec.class, Integer.parseInt(id));
		return cpaUtil.getSpecFilePath(spec);
	}
	@Override
	public void saveCpaSpec(CtnCpaSpec spec) {
		 getHibernateTemplate().persist(spec);
	}

	@SuppressWarnings("unchecked")
	@Override
	public CtnCpaSpec getCpaSpecByExample(CtnCpaSpec spec) {
		List list=getHibernateTemplate().findByExample(spec);
		Collections.sort(list);
		return list.isEmpty()?null:(CtnCpaSpec) list.get(list.size()-1);
	}

}
